package br.com.portalecbahia.dao;

import java.util.ArrayList;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import br.com.portalecbahia.model.Stadium;
import br.com.portalecbahia.util.PortalECBahiaUtil;


public class StadiumDAO {
	
	
	private static final String LOG_TAG = "StadiumDAO";
	private Context context;
	private SQLiteDatabase database;
	private DBHelper dbHelper;

	public StadiumDAO(Context context) {
		this.context = context;
	}

	public StadiumDAO openWritable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getWritableDatabase();
		return this;
	}
	
	public StadiumDAO openReadable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getReadableDatabase();
		return this;
	}

	public void close() {
		dbHelper.close();
	}


	public List<Stadium> findAll () {
		this.openReadable();

		List<Stadium> result = new ArrayList<Stadium>();		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("SELECT * FROM tbl_stadium order by ID", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				do{
					result.add(this.bindSQLite(cursor));
				}while (cursor.moveToNext());
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(LOG_TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	
	
	public Stadium findByPrimaryKey (Stadium stadium) {
		this.openReadable();

		Cursor cursor;
		Stadium retorno = null;	 
		try{
			cursor = database.rawQuery("SELECT * FROM tbl_stadium WHERE ID = "+stadium.getId(), null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				retorno =  this.bindSQLite(cursor);
				
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(LOG_TAG, e.toString());
		}
		
		this.close();
		
		 return retorno;
	}

	
	private Stadium bindSQLite(Cursor cursor){
		Stadium stadium  = new Stadium();
		
		for (int i = 0; i < cursor.getColumnCount(); i++){

			if("ID".equals(cursor.getColumnName(i)))
				stadium.setId(cursor.getLong(i));
			else if("name".equals(cursor.getColumnName(i))) 
				stadium.setName(cursor.getString(i));
			else if("capacity".equals(cursor.getColumnName(i)))
				stadium.setCapacity(cursor.getString(i));
			else if("urlImage".equals(cursor.getColumnName(i)))
				stadium.setUrlImage(cursor.getString(i));
			else if("dateInauguration".equals(cursor.getColumnName(i)))
				stadium.setDateInauguration(cursor.getString(i));
			else if("latitude".equals(cursor.getColumnName(i)))
				stadium.setLatitude(cursor.getString(i));
			else if("longitude".equals(cursor.getColumnName(i)))
				stadium.setLongitude(cursor.getString(i));
			else if("description".equals(cursor.getColumnName(i)))
				stadium.setDescription(cursor.getString(i));
				
				
		}
		return stadium;
	}

	public void insert(JSONArray jsonArray) throws JSONException {
		this.openWritable();
		Cursor cursor = null;
		try{
			database.beginTransaction();
			for (int i = 0; i < jsonArray.length(); i++) {
				JSONObject json = jsonArray.getJSONObject(i);
				


				cursor = database.rawQuery(" INSERT INTO tbl_stadium (ID,  name ,  capacity ,  urlImage ,  dateInauguration ,  latitude ,  longitude, description )  " +
						" VALUES ("+json.getLong("id")+", '"+json.getString("name")+"', '"+json.getString("capacity")+"', '"+json.getString("url_image")+"', '"+json.getString("dtc_inauguration")+"', '"+json.getString("latitude")+"', '"+json.getString("longitude")+"', '"+json.getString("description")+"');", null);
	
					cursor.moveToFirst();
					cursor.close();
				
			
			}
		}catch (SQLException e) {
			Log.e(LOG_TAG, e.toString());
		}finally {
			database.setTransactionSuccessful();
			database.endTransaction();
		}
		
		this.close();
		
	}
	
	public void removeAll() {
		this.openWritable();
		Cursor cursor = null;
		try{
			cursor = database.rawQuery("DELETE FROM tbl_stadium", null);
			cursor.moveToFirst();
			cursor.close();
		}catch (SQLException e) {
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		this.close();
		
	}

	


	
}

